package database;

import java.sql.*;
import java.text.DateFormatSymbols;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Map;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;

import oggetti.Cliente;
import oggetti.Magazzino;
import oggetti.Riparazione;
import oggetti.Utente;

public class DB {

    private Statement stat;
    private Connection conn;
    private PreparedStatement prep;

    public DB() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn =
                    //DriverManager.getConnection("jdbc:mysql://127.0.0.1:8889/tony","bobo","bobo");
                    DriverManager.getConnection("jdbc:mysql://95.240.77.74:3306/moveitsalerno", "moveitsalerno", "j9f385r50");
                    //15121986DriverManager.getConnection("jdbc:mysql://192.168.1.100:3306/moveitsalerno", "moveitsalerno", "j9f385r50");
            stat = conn.createStatement();

        } catch (Exception e) {
            System.out.println("Error: " + e);
        }
    }

    public Map<String, Double> getGuadagni() {
        try {
            Map<String, Double> guad = new TreeMap<String, Double>();
            ArrayList<String> tables = new ArrayList<String>();
            DatabaseMetaData dbmd = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet resultSet = dbmd.getTables(null, null, "%", types);
            while (resultSet.next()) {
                String tableName = resultSet.getString(3);
                if (tableName.contains("-")) {
                    tables.add(tableName);
                }
            }
            if(tables.size()>0){
               for(String table:tables){
                  ResultSet rs = stat.executeQuery("SELECT * FROM `"+table+"`");
                  rs.next();
                  String tmp[] = table.split("-");
                  String mese = new DateFormatSymbols().getMonths()[Integer.parseInt(tmp[0]) -1];
                  guad.put(mese+" - "+tmp[1], rs.getDouble("guadagno"));
               }
            }else
                return null;
            return guad;
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public String getDocumenti() {
        String id = "";
        int tmp = 1;
        try {
            tmp = new DB().queryCount("SELECT * FROM documenti");
            id = "" + (tmp == 0 ? 1 : tmp);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }

    public int getFreeIdAccessorio() {
        try {
            ResultSet rs = stat.executeQuery("SELECT MAX(id) as free FROM magazzino");
            rs.next();
            int idNew = rs.getInt("free");
            return idNew + 1;
        } catch (SQLException e) {
            return 0;
        }
    }

    public boolean addRiparazioneToUtente(int idScheda, String username) {
        try {
            PreparedStatement prep = conn.prepareStatement("INSERT INTO utente_riparazione VALUES(?,?)");
            prep.setString(1, username);
            prep.setInt(2, idScheda);
            return prep.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public ArrayList<Magazzino> getAccessori() {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM magazzino");

            ArrayList<Magazzino> c = new ArrayList<Magazzino>();
            while (rs.next()) {
                c.add(new Magazzino(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public ArrayList<Magazzino> getResultFromMagazzino(String chiave, String valore) {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM magazzino WHERE " + chiave + " LIKE '%" + valore + "%'");

            ArrayList<Magazzino> c = new ArrayList<Magazzino>();
            while (rs.next()) {
                c.add(new Magazzino(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public ArrayList<Cliente> getResultFromClienti(String chiave, String valore) {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM clienti WHERE " + chiave + " LIKE '%" + valore + "%' AND valid='yes'");
            ArrayList<Cliente> c = new ArrayList<Cliente>();
            while (rs.next()) {
                c.add(new Cliente(rs.getString(2), rs.getString(3), rs.getString(6), rs.getString(7), rs.getString(4), rs.getString(5), rs.getString(8), Integer.parseInt(rs.getString(1)), rs.getString(9), rs.getString(10)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public ArrayList<Magazzino> getResultFromAccessori(String chiave, String valore) {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM magazzino WHERE " + chiave + " LIKE '%" + valore + "%'");
            ArrayList<Magazzino> c = new ArrayList<Magazzino>();
            while (rs.next()) {
                c.add(new Magazzino(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public ArrayList<Riparazione> getResultFromRiparazioni(String chiave, String valore) {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM riparazione WHERE " + chiave + " LIKE '%" + valore + "%'");
            ArrayList<Riparazione> c = new ArrayList<Riparazione>();
            while (rs.next()) {
                c.add(new Riparazione(Integer.parseInt(rs.getString(1)), Integer.parseInt(rs.getString(3)), Double.parseDouble(rs.getString(5)), rs.getString(2), rs.getString(4), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public boolean salvaModificheCliente(Cliente c) {
        String query = "UPDATE clienti SET nome=?,cognome=?,email=?,residenza=?,telefono=?,fiscale=?,iva=? WHERE id=?";
        try {
            prep = conn.prepareStatement(query);
            prep.setString(1, c.getNome());
            prep.setString(2, c.getCognome());
            prep.setString(3, c.getEmail());
            prep.setString(4, c.getResidenza());
            prep.setString(5, c.getTelefono());
            prep.setString(6, c.getFiscale());
            prep.setString(7, c.getIva());
            prep.setString(8, "" + c.getId());
            prep.execute();
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            chiudiConnessione();
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public boolean cancellaUtente(String username) {
        try {
            PreparedStatement prep = conn.prepareStatement("DELETE FROM utente WHERE username=?;");
            prep.setString(1, username);
            prep.execute();
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            chiudiConnessione();
            e.printStackTrace();
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public boolean salvaModificheUtente(Utente c) {
        String query = "UPDATE clienti SET nome=?,cognome=?,email=?,username=?,password=? WHERE username=?";
        try {
            prep = conn.prepareStatement(query);
            prep.setString(1, c.getNome());
            prep.setString(2, c.getCognome());
            prep.setString(3, c.getEmail());
            prep.setString(4, c.getUsername());
            prep.setString(5, c.getPassword());
            prep.setString(6, c.getUsername());
            prep.execute();
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            chiudiConnessione();
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public boolean salvaModificheRiparazione(Riparazione r) {
        String query = "UPDATE riparazione SET difetto=?,imei=?,note=?,dataConsegna=?,username=?,stato=?,preventivo=? WHERE scheda=?";
        try {
            prep = conn.prepareStatement(query);
            prep.setString(1, r.getDifetto());
            prep.setString(2, r.getImei());
            prep.setString(3, r.getNote());
            prep.setString(4, r.getDataConsegna());
            prep.setString(5, r.getUtente());
            prep.setString(6, r.getStato());
            prep.setString(7, "" + r.getPreventivo());
            prep.setString(8, "" + r.getScheda());
            prep.execute();
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            chiudiConnessione();
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public Utente getUtenteFromUsername(String user) {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM utente WHERE username='" + user + "';");
            rs.next();
            Utente u = new Utente(user, rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
            chiudiConnessione();
            return u;
        } catch (SQLException e) {
            chiudiConnessione();
            e.printStackTrace();
            return null;
        }
    }

    public Utente getUtente(String username, String password) {
        ResultSet rs;
        try {
            rs = stat.executeQuery("SELECT * FROM utente WHERE username='" + username + "' AND password='" + password + "';");
            rs.next();
            return new Utente(username, password, rs.getString(3), rs.getString(4), rs.getString(5));
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    private int getFreeIdDocumentoTrasporto() throws SQLException {
        ResultSet rs = stat.executeQuery("SELECT MAX(scheda) FROM documenti");
        rs.next();
        return rs.getInt(1);

    }

    public int addDocumentoTrasporto(String destinatario, String time) {
        try {
            int id = getFreeIdDocumentoTrasporto() + 1;
            prep = conn.prepareStatement("insert into documenti values (?,?,?);");
            prep.setInt(1, id);
            prep.setString(2, time);
            prep.setString(3, destinatario);
            prep.execute();
            chiudiConnessione();
            return id;
        } catch (SQLException e) {
            chiudiConnessione();
            e.printStackTrace();
            return -1;
        } finally {
            chiudiConnessione();
        }
    }

    public boolean addUtente(Utente u) {
        try {
            prep = conn.prepareStatement("insert into utente values (?,?,?,?,?);");
            prep.setString(1, u.getUsername());
            prep.setString(2, u.getPassword());
            prep.setString(3, u.getNome());
            prep.setString(4, u.getCognome());
            prep.setString(5, u.getEmail());
            prep.execute();
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            chiudiConnessione();
            e.printStackTrace();
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public void cancellaAccessorio(int id) {
        try {
            PreparedStatement prep = conn.prepareStatement("DELETE FROM magazzino WHERE id=?");
            prep.setInt(1, id);
            prep.execute();
            chiudiConnessione();
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
        } finally {
            chiudiConnessione();
        }
    }

    public boolean addAccessorio(Magazzino u) {
        try {
            prep = conn.prepareStatement("insert into magazzino values (?,?,?,?,?,?,?,?);");
            prep.setInt(1, u.getId());
            prep.setString(2, u.getMarca());
            prep.setString(3, u.getModello());
            prep.setString(4, u.getImei());
            prep.setString(5, u.getSeriale());
            prep.setString(6, u.getPrezzoAcq());
            prep.setString(7, u.getPrezzoRiv());
            prep.setString(8, u.getCondizione());
            prep.execute();
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            chiudiConnessione();
            e.printStackTrace();
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public void cancellaCliente(String id) {
        try {
            PreparedStatement prep = conn.prepareStatement("UPDATE clienti SET valid=? WHERE id=?;");
            prep.setString(1, "no");
            prep.setString(2, id);
            prep.execute();
            prep = conn.prepareStatement("UPDATE riparazione SET valid=? WHERE idCliente=?;");
            prep.setString(1, "no");
            prep.setString(2, id);
            prep.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            chiudiConnessione();
        }
    }

    private void chiudiConnessione() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /*
     * private void addIDinFree(String id){ try { prep =
     * conn.prepareStatement("insert into id_liberi_clienti values (?);");
     * prep.setString(1, id); prep.execute();	* } catch (SQLException e) {
     * e.printStackTrace(); }finally{ chiudiConnessione(); }
     *
     * }
     */

    public boolean addRiparazione(Riparazione r) {
        try {
            prep = conn.prepareStatement("insert into riparazione values (?,?,?,?,?,?,?,?,?,?,?);");
            prep.setString(1, "" + r.getScheda());
            prep.setString(2, r.getImei());
            prep.setString(3, "" + r.getIdCliente());
            prep.setString(4, r.getDifetto());
            prep.setString(5, "" + r.getPreventivo());
            prep.setString(6, r.getStato());
            prep.setString(7, r.getUtente());
            prep.setString(8, r.getDataConsegna());
            prep.setString(9, r.getAccettato());
            prep.setString(10, r.getNote());
            prep.setString(11, "yes");
            prep.execute();
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            chiudiConnessione();
            e.printStackTrace();
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public String getIDLiberiRiparazioni() throws SQLException {

        ResultSet rs = stat.executeQuery("SELECT * FROM id_liberi_riparazioni");

        int count = 0;
        while (rs.next()) {
            count++;
        }
        if (count > 0) {
            rs = stat.executeQuery("SELECT * FROM id_liberi_riparazioni");
            String a = rs.getString("id");
            chiudiConnessione();
            return a;
        } else {
            chiudiConnessione();
            return "";
        }

    }
    /*
     * public String getIDLiberi() throws SQLException{
     *
     * ResultSet rs = stat.executeQuery("SELECT * FROM id_liberi_clienti");
     *
     * int count = 0; while(rs.next()){ count++; } if(count>0){ rs =
     * stat.executeQuery("SELECT * FROM id_liberi_clienti"); String a =
     * rs.next()?rs.getString("id"):""; chiudiConnessione(); return a; }else{
     * chiudiConnessione(); return ""; }
     *
     * }
     */

    public int queryCount(String query, String... arg) throws SQLException {
        prep = conn.prepareStatement(query);
        ResultSet rs = prep.executeQuery();

        int count = 0;
        while (rs.next()) {
            count++;
        }
        chiudiConnessione();
        return count;
    }

    public ArrayList<Utente> getUtenti() {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM utente");
            ArrayList<Utente> c = new ArrayList<Utente>();
            while (rs.next()) {
                c.add(new Utente(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public ArrayList<Riparazione> getRiparzioni() {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM riparazione WHERE valid='yes'");

            ArrayList<Riparazione> c = new ArrayList<Riparazione>();
            while (rs.next()) {
                c.add(new Riparazione(Integer.parseInt(rs.getString(1)), Integer.parseInt(rs.getString(3)), Double.parseDouble(rs.getString(5)), rs.getString(2), rs.getString(4), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public ArrayList<Cliente> getClienti() {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM clienti WHERE valid='yes'");

            ArrayList<Cliente> c = new ArrayList<Cliente>();
            while (rs.next()) {
                c.add(new Cliente(rs.getString(2), rs.getString(3), rs.getString(6), rs.getString(7), rs.getString(4), rs.getString(5), rs.getString(8), Integer.parseInt(rs.getString(1)), rs.getString(9), rs.getString(10)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }

    public Riparazione getRiparazioneFromScheda(int scheda) {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM riparazione WHERE scheda='" + scheda + "';");
            Riparazione c = null;
            rs.next();
            int idCliente = Integer.parseInt(rs.getString(3));
            Double preventivo = Double.parseDouble(rs.getString(5));
            c = new Riparazione(scheda, idCliente, preventivo, rs.getString(2), rs.getString(4), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10));
            chiudiConnessione();
            return c;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public Cliente getClienteFromID(String id) throws SQLException {
        ResultSet rs = stat.executeQuery("SELECT * FROM clienti WHERE id='" + id + "' AND valid='yes';");
        Cliente c = null;
        rs.next();
        c = new Cliente(rs.getString(2), rs.getString(3), rs.getString(6), rs.getString(7), rs.getString(4), rs.getString(5), rs.getString(8), Integer.parseInt(rs.getString(1)), rs.getString(9), rs.getString(10));
        chiudiConnessione();
        return c;
    }

    public boolean addCliente(Cliente c) {
        try {
            PreparedStatement prep = conn.prepareStatement(
                    "insert into clienti values (?, ?, ?, ?, ?, ?, ?, ?,?,?,?);");
            prep.setString(1, "" + c.getId());
            prep.setString(2, c.getNome());
            prep.setString(3, c.getCognome());
            prep.setString(4, c.getResidenza());
            prep.setString(5, c.getTelefono());
            prep.setString(6, c.getIva());
            prep.setString(7, c.getFiscale());
            prep.setString(8, c.getEmail());
            prep.setString(9, c.getUsername());
            prep.setString(10, c.getPassword());
            prep.setString(11, "yes");
            prep.execute();

            ResultSet rs = stat.executeQuery("SELECT * FROM id_liberi_clienti WHERE id='" + c.getId() + "';");

            int count = rs.next() ? 1 : 0;
            if (count > 0) {
                prep = conn.prepareStatement("DELETE FROM id_liberi_clienti WHERE id=?;");
                prep.setString(1, "" + c.getId());
                prep.execute();
            }
            chiudiConnessione();
            return true;
        } catch (SQLException e) {
            return false;
        } finally {
            chiudiConnessione();
        }
    }

    public void queryUpdate(String query) {
        try {
            prep = conn.prepareStatement(query);
            prep.executeUpdate();
            chiudiConnessione();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean addGuadagnoToMese(int mese, int anno, double preventivo) {
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet resultSet = dbmd.getTables(null, null, "%", types);
            boolean flag = false;
            String table = mese + "-" + anno;
            while (resultSet.next() && !flag) {
                String tableName = resultSet.getString(3);
                if (tableName.equals(table)) {
                    flag = true;
                }
            }
            if (!flag) {
                stat.executeUpdate("CREATE TABLE `" + table + "` (`guadagno` DOUBLE NOT NULL);");
                PreparedStatement prep = conn.prepareStatement("INSERT INTO `" + table + "` VALUES(?)");
                prep.setDouble(1, 0.0);
                prep.execute();
            }
            ResultSet rs = stat.executeQuery("SELECT * FROM `" + table + "`");
            rs.next();
            double oldSum = rs.getDouble("guadagno");
            double newSum = oldSum + preventivo;
            PreparedStatement prep = conn.prepareStatement("UPDATE `" + table + "` SET `guadagno`=? WHERE `guadagno`=?");
            prep.setDouble(1, newSum);
            prep.setDouble(2, oldSum);
            prep.execute();
            return true;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public ArrayList<Riparazione> getRiparzioniFromCliente(int cliente) {
        try {
            ResultSet rs = stat.executeQuery("SELECT * FROM riparazione WHERE idCliente='" + cliente + "' AND valid='yes'");
            ArrayList<Riparazione> c = new ArrayList<Riparazione>();
            while (rs.next()) {
                c.add(new Riparazione(Integer.parseInt(rs.getString(1)), Integer.parseInt(rs.getString(3)), Double.parseDouble(rs.getString(5)), rs.getString(2), rs.getString(4), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10)));
            }
            chiudiConnessione();
            return c.size() > 0 ? c : null;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            chiudiConnessione();
            return null;
        } finally {
            chiudiConnessione();
        }
    }
}
